﻿CREATE PROCEDURE [dbo].[Report_CategoryComparisonByStores]
	@RangeReviewId int,
	@CategoryId smallint, 
	@ExchangeRate smallint = 0
AS


declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @totalCoverage INT = @totalMonths 

begin try drop table #exchangeRates; end try begin catch end catch;

Select 
	months.Date
	, CASE 
		WHEN @ExchangeRate = 0 THEN 1 --Moneda Local
		WHEN @ExchangeRate = 1 THEN ISNULL(ex.Rate,1) --Rate para Average
		WHEN @ExchangeRate = 2 THEN ISNULL(ex.ForexRate,1) --Rate para Same Store
		ELSE 1
	END AS Rate
Into #exchangeRates
From Months months
INNER JOIN dbo.RangeReviews rr WITH (NOLOCK) on rr.Id = @RangeReviewId
INNER JOIN dbo.Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId
LEFT OUTER JOIN dbo.CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = months.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
Where months.Date BETWEEN @startDate AND @endDate


SELECT
	  st.Id
	, st.Code
	, st.Name
	, st.Region
	, st.Subregion
	, st.City
	, st.State
	, st.SEL
	, st.ShoppingOccasion
	, st.Size
	, st.Address
	, st.Phone
	, st.Fax
	, st.Format
	, st.PBL
	, st.Mail
	, st.Contact
	, st.SiteLocation
	, st.ShopType
	, st.Layout
	, st.OpeningHours
	, st.Attribute1
	, st.Attribute2
	, parentCat.Name 'Category'
	, CASE WHEN umc.Id IS NOT NULL THEN ISNULL(SUM(cast(Units as money) / umc.Value) / CAST(@totalCoverage AS MONEY),0) ELSE ISNULL(SUM(cast(Units as money)) / CAST(@totalCoverage AS MONEY),0) END AS Units
	, CASE WHEN co.IsVATIncluded = 0 THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) ELSE ISNULL(SUM(Sales / (1 + ar.TaxRate) * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS Sales	
	, CASE WHEN am.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * am.Margin 
		   WHEN scm.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * scm.Margin 
		   WHEN cm.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate) / CAST(@totalCoverage AS MONEY),0) * cm.Margin 
		   ELSE ISNULL(SUM(GM * ex.Rate) / CAST(@totalCoverage AS MONEY),0) END AS GM	
	, ISNULL(SUM(inv.Cost * ex.Rate) / CAST(@totalCoverage AS MONEY),0) as InventoryCost
FROM Stores st WITH (NOLOCK)
	INNER JOIN RangeReviewStores rrs WITH (NOLOCK) on rrs.StoreId = st.Id and rrs.RangeReviewId = @RangeReviewId
	INNER JOIN RangeReviews rr WITH (NOLOCK) on rr.Id = rrs.RangeReviewId
	INNER JOIN Articles ar WITH (NOLOCK) on ar.RangeReviewId = rr.Id
	INNER JOIN Categories cat WITH (NOLOCK) on ar.CategoryId = cat.Id 
							and (@CategoryId = 0 OR ar.CategoryId = @CategoryId OR cat.ParentId = @CategoryId) 
	INNER JOIN Categories parentCat WITH (NOLOCK) on cat.ParentId = parentCat.Id and parentCat.ParentId = 6
	INNER JOIN Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId
	INNER JOIN Countries co WITH (NOLOCK) on rr.CountryId = co.Id	
	INNER JOIN Months months WITH (NOLOCK) on (months.Date BETWEEN @startDate AND @endDate)
	INNER JOIN #exchangeRates ex on months.Date = ex.Date
	LEFT JOIN ArticleSales sales WITH (NOLOCK)  on sales.ArticleId = ar.Id 
		AND sales.Date = months.Date 
		AND sales.Confirmed = 1
		AND sales.StoreId = st.Id
	LEFT JOIN Inventories inv WITH (NOLOCK)  on inv.ArticleId = ar.Id 
		AND inv.Date = months.Date 
		AND inv.StoreId = st.Id
	--LEFT OUTER JOIN CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = months.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
	LEFT OUTER JOIN UnitOfMeasureConvertions umc WITH (NOLOCK) on ar.UnitOfMeasureConvertionId = umc.Id and ar.UOM = umc.SourceUnit
	LEFT JOIN TheoreticalMargins am WITH (NOLOCK)  on am.CountryId = co.Id AND  am.ArticleId = ar.Id  
	LEFT JOIN TheoreticalMargins scm WITH (NOLOCK) on scm.CountryId = co.Id AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
	LEFT JOIN TheoreticalMargins cm WITH (NOLOCK)  on cm.CountryId = co.Id AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)
Where
	ar.IsDeletedPerformance = 0
Group by
	  st.Id
	, st.Code
	, st.Name
	, st.Region
	, st.Subregion
	, st.City
	, st.State
	, st.SEL
	, st.ShoppingOccasion
	, st.Size
	, st.Address
	, st.Phone
	, st.Fax
	, st.Format
	, st.PBL
	, st.Mail
	, st.Contact
	, st.SiteLocation
	, st.ShopType
	, st.Layout
	, st.OpeningHours
	, st.Attribute1
	, st.Attribute2
	, parentCat.Name
	, co.IsVATIncluded
	, umc.Id
	, umc.TargetUnit
	, umc.Value
	,am.Margin
	,scm.Margin
	,cm.Margin
Order By
	st.Code  DESC